package com.qf.book.dao.ipml;

import com.qf.book.dao.BookDao;
import com.qf.book.domain.Book;
import com.qf.book.util.DBUtils;

import java.math.BigDecimal;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class BookDaoImpl implements BookDao {
    @Override
    public List<Book> selectAll(){
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        try {
            connection = DBUtils.getConnection();
            String sql = "SELECT id,title,author,publicDate,publisher,isbn,price,picture,cid FROM book;";
            ps = connection.prepareStatement(sql);
            resultSet = ps.executeQuery();
            ArrayList<Book> books = new ArrayList<>();
            while (resultSet.next()){
                int id = resultSet.getInt("id");
                String title = resultSet.getString("title");
                String author = resultSet.getString("author");
                Timestamp publicDate = resultSet.getTimestamp("publicDate");
                String publisher = resultSet.getString("publisher");
                String isbn = resultSet.getString("isbn");
                BigDecimal price = resultSet.getBigDecimal("price");
                String picture = resultSet.getString("picture");
                int cid = resultSet.getInt("cid");
                //封装实体类
                Book book = new Book(id,title,author,publicDate,publisher,isbn,price,picture,cid);
                //向集合中添加元素
                books.add(book);
            }
            return books;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBUtils.closeAll(resultSet,ps,connection);
        }

    }

    @Override
    public List<Book> selectBytitle(String title) {
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        try {
            connection = DBUtils.getConnection();
            String sql = "SELECT id,title,author,publicDate,publisher,isbn,price,picture,cid FROM book WHERE title LIKE ? ;";
            ps = connection.prepareStatement(sql);
            ps.setString(1,"%"+title+"%");
            resultSet = ps.executeQuery();
            ArrayList<Book> books = new ArrayList<>();
            while (resultSet.next()){
                int id = resultSet.getInt("id");
                String title1 = resultSet.getString("title");
                String author = resultSet.getString("author");
                Timestamp publicDate = resultSet.getTimestamp("publicDate");
                String publisher = resultSet.getString("publisher");
                String isbn = resultSet.getString("isbn");
                BigDecimal price = resultSet.getBigDecimal("price");
                String picture = resultSet.getString("picture");
                int cid = resultSet.getInt("cid");
                //封装实体类
                Book book = new Book(id,title1,author,publicDate,publisher,isbn,price,picture,cid);
                //向集合中添加元素
                books.add(book);
            }
            return books;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBUtils.closeAll(resultSet,ps,connection);
        }
    }

    @Override
    public void insert(Book book) {
        Connection connection = null;
        PreparedStatement ps = null;
        try {
            connection = DBUtils.getConnection();
            String sql ="insert into book (title,author,publicDate,publisher,isbn,price,picture,cid ) values (?,?,?,?,?,?,?,?)";
            ps = connection.prepareStatement(sql);
            ps.setString(1,book.getTitle());
            ps.setString(2,book.getAuthor());
            ps.setObject(3,book.getPublicDate());
            ps.setString(4,book.getPublisher());
            ps.setString(5,book.getIsbn());
            ps.setBigDecimal(6,book.getPrice());
            ps.setString(7,book.getPicture());
            ps.setInt(8,book.getCid());
            int i = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.closeAll(null,ps,connection);
        }
    }

    @Override
    public Book selectById(int id) {
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        try {
            connection = DBUtils.getConnection();
            String sql = "SELECT id,title,author,publicDate,publisher,isbn,price,picture,cid FROM book WHERE id = ? ;";
            ps = connection.prepareStatement(sql);
            ps.setInt(1,id);
            resultSet = ps.executeQuery();
            Book book = null;
            if (resultSet.next()){
                int id1 = resultSet.getInt("id");
                String title = resultSet.getString("title");
                String author = resultSet.getString("author");
                Timestamp publicDate = resultSet.getTimestamp("publicDate");
                String publisher = resultSet.getString("publisher");
                String isbn = resultSet.getString("isbn");
                BigDecimal price = resultSet.getBigDecimal("price");
                String picture = resultSet.getString("picture");
                int cid = resultSet.getInt("cid");
                //封装实体类
                book = new Book(id,title,author,publicDate,publisher,isbn,price,picture,cid);
            }
            return book;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            DBUtils.closeAll(resultSet,ps,connection);
        }

    }

    @Override
    public void update(Book book) {
        Connection connection = null;
        PreparedStatement ps = null;
        try {
            connection = DBUtils.getConnection();
            String sql ="update book set title=?,author=?,publicDate=?,publisher=?,isbn=?,price=?,picture=?,cid=? where id= ? ";
            ps = connection.prepareStatement(sql);
            ps.setString(1,book.getTitle());
            ps.setString(2,book.getAuthor());
            ps.setObject(3,book.getPublicDate());
            ps.setString(4,book.getPublisher());
            ps.setString(5,book.getIsbn());
            ps.setBigDecimal(6,book.getPrice());
            ps.setString(7,book.getPicture());
            ps.setInt(8,book.getCid());
            ps.setInt(9,book.getId());
            int i = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.closeAll(null,ps,connection);
        }
    }

    @Override
    public void delete(int bookId) {
        Connection connection = null;
        PreparedStatement ps = null;
        try {
            connection = DBUtils.getConnection();
            String sql ="delete from  book  where id= ? ";
            ps = connection.prepareStatement(sql);
            ps.setInt(1,bookId);
            int i = ps.executeUpdate();
            if (i==0){
                throw new RuntimeException("书籍不存在");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.closeAll(null,ps,connection);
        }
    }
}
